
package applogic;
import java.sql.*;
import java.util.*;




public class Storage
{
	Connection conn;
	
	//Connect to the database in the same folder as this class. Starts the embeded driver.
    public Storage()
    {

        	try
        	{
        		Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
        		conn = DriverManager.getConnection("jdbc:derby:c:\\database;");
        	}
        	catch (Exception e)
        	{
        		throw new RuntimeException("Unable to connect", e);
        	}
        
    }

    
 
	
    // Saves a meeting object to the database.
    public void saveMeeting(Meeting meeting){
      	try
    	{
	    	Statement stmt = conn.createStatement();
	    	System.out.println(meeting.toSQL());
	    	stmt.executeUpdate(meeting.toSQL());
	 
	    
    	}
    	catch (Exception e)
    	{
    		
    		e.printStackTrace();
    	}
    	
	
    }
    // Saves an appointment to the database from an appointment object.
    public void saveAppointment(Appointment appointment){
      	try
    	{
	    	Statement stmt = conn.createStatement();
	    	stmt.executeUpdate(appointment.toSQL());
	 
	    
    	}
    	catch (Exception e)
    	{
    		
    		e.printStackTrace();
    	}
    	
	
    }
    
    
    //Adds a user to a meeting in the database.
    public void saveRsvp(User user, Meeting meeting){
      	try
    	{
	    	Statement stmt = conn.createStatement();
	    	stmt.executeUpdate("INSERT INTO attends(username,aid,status,description) VALUES('"+user.getUser()+"',"+meeting.getID()+",0,'')");  

	   
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
    	
	
    }
    
    
    //Change the rsvp int in the database .
    public void editRsvp(Rsvp rsvp){
      	try
    	{
	    	Statement stmt = conn.createStatement();
	    	stmt.executeUpdate("UPDATE attends SET status = "+rsvp.getRsvp()+",description = '"+rsvp.getDescription()+"'  WHERE username='"+rsvp.getUser().getUser()+"' AND AID = "+rsvp.getAid()+"");
	    	
	 
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
    	
    	   
    }
    
    //Sets a meeting to canceled.
    public void deleteMeeting(Meeting meeting){
    	try
    	{
	    	Statement stmt = conn.createStatement();
	    	stmt.executeUpdate("UPDATE meeting SET cancel = 1 WHERE aid="+meeting.getID()+"");
	 
	    
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
    	
    	
    }
    
    //Delete an appointment from the database.
    public void deleteAppointment(Appointment appointment){
       	try
    	{
	    	Statement stmt = conn.createStatement();
	    	stmt.executeUpdate("DELETE FROM appointment WHERE aid= "+appointment.getID()+"");
	    
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
    }
    
    //Removes a user from attending a meeting.
    public void deleteUser(User user, Meeting meeting){
       	try
    	{
	    	Statement stmt = conn.createStatement();
	    	stmt.executeUpdate("DELETE FROM attends WHERE aid= "+meeting.getID()+" AND username='"+user.getUser()+"'");
	    
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
    }
    
    
    //Gets an arraylist with all the users in the database.
    public HashMap<String,User> getAllUsers(){
        {
        	HashMap<String,User>  result = new HashMap<String,User>();
        	try
        	{
    	    	Statement stmt = conn.createStatement();
    	    	ResultSet rs = stmt.executeQuery("select * from users");
    	    	while (rs.next())
    	    	{
    	    		String username = rs.getString("username").substring(0,rs.getString("username").indexOf(' ')).toLowerCase();
    	    		String password = rs.getString("password").substring(0,rs.getString("password").indexOf(' ')).toLowerCase();
    	    		result.put(
    	   				username,
    	   				new User(username,password)
    	   				);
    	    	}
    	    	rs.close();
        	}
        	catch (Exception e)
        	{
        		e.printStackTrace();
        	}
        	return result; 
        	
        }
        
    	
    }
    
    
    //Creates an arraylist with all the rooms in the database.
    public HashMap<Integer,Room> getAllRooms()
    {
    	HashMap<Integer,Room> result = new HashMap<Integer,Room>();
    	try
    	{
	    	Statement stmt = conn.createStatement();
	    	ResultSet rs = stmt.executeQuery("select * from room");
	    	while (rs.next())
	    	{

	   		result.put(rs.getInt("rid"),new Room(rs.getInt("rid"), rs.getInt("capacity")));
	    	}
	    	rs.close();
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
    	return result;
    }
    
    //Gets an arraylist with all the appointments in the database.
    public HashMap<Integer,Appointment> getAllAppointments(){
    	   {
    		   HashMap<Integer,Appointment> result = new HashMap<Integer,Appointment>();
    	    	try
    	    	{
    		    	Statement stmt = conn.createStatement();
    		    	ResultSet rs = stmt.executeQuery("select * from appointment");
    		    	while (rs.next())
    		    	{
    		    		String user = rs.getString("holds");
    		    		user = user.substring(0,user.indexOf(' '));
    		    		result.put(rs.getInt("aid"),new Appointment(rs.getInt("aid"), rs.getInt("day"), rs.getInt("month"), rs.getInt("start"), rs.getInt("finish"), rs.getString("description"),
     		   				rs.getString("placeDescription"),new User(user,"")));
    		    	}
    		    	rs.close();
    	    	}
    	    	catch (Exception e)
    	    	{
    	    		e.printStackTrace();
    	    	}
    	    	return result;
    	    	}
    	   
    	    }
   
   
    //Gets all the meetings and puts them in an arraylist.
    public HashMap<Integer,Meeting> getAllMeetings(){
    	   {
    		   HashMap<Integer,Meeting> result = new HashMap<Integer,Meeting>();
    	    	try
    	    	{
    		    	Statement stmt = conn.createStatement();
    		    	ResultSet rs = stmt.executeQuery("select * from meeting");
    		    	while (rs.next())
    		    	{
    		    		String user = rs.getString("holds");
    		    		user = user.substring(0,user.indexOf(' '));
    		    		result.put(rs.getInt("aid"),new Meeting(rs.getInt("aid"), rs.getInt("day"), rs.getInt("month"), rs.getInt("start"), rs.getInt("finish"), rs.getString("description"),
    		   				rs.getInt("isin"), rs.getInt("externalAttendees"), rs.getInt("cancel"), new User(user,""), null, "all_accepted"));
    		    	}
    		    	rs.close();
    	    	}
    	    	catch (Exception e)
    	    	{
    	    		e.printStackTrace();
    	    	}
    	    	return result;
    	   	}
    }
    
    public HashMap<String,HashMap<Integer,Rsvp>> getAllRsvp(){
 	   {
 		  HashMap<String,HashMap<Integer,Rsvp>> result = new HashMap<String,HashMap<Integer,Rsvp>>();
 	    	try
 	    	{
 		    	Statement stmt = conn.createStatement();
 		    	ResultSet rs = stmt.executeQuery("select * from attends");
 		    	while (rs.next())
 		    	{
 		    		String username = rs.getString("username");
 		    		username = username.substring(0,username.indexOf(' '));
 		    		if(!result.containsKey(username))result.put(username, new HashMap<Integer,Rsvp>());
 		    		result.get(username).put(rs.getInt("aid"),new Rsvp(rs.getInt("aid"), new User(username,""), rs.getInt("status"), rs.getString("description")));
 		    	}
 		    	rs.close();
 	    	}
 	    	catch (Exception e)
 	    	{
 	    		e.printStackTrace();
 	    	}
 	    	return result;
 	   	}
 }
    
    
    //Get a meeting corresponding to a given meeting ID.
    public Meeting getMeeting(int meetingID){
    	 {
 	    	Meeting result = null;
 	    	try
 	    	{
 		    	Statement stmt = conn.createStatement();
 		    	ResultSet rs = stmt.executeQuery("select * from meeting where aid="+meetingID+"");
 		    	while (rs.next())
 		    	{

 		    		result = new Meeting(rs.getInt("aid"), rs.getInt("day"), rs.getInt("month"), rs.getInt("start"), rs.getInt("finish"), rs.getString("description"),
    		   				rs.getInt("isin"), rs.getInt("externalAttendees"), rs.getInt("cancel"), null, null, null);
 		    	}
 		    	rs.close();
 	    	}
 	    	catch (Exception e)
 	    	{
 	    		e.printStackTrace();
 	    	}
 	    	return result;
 	   	} 
    }
    	
    
    //Get an appointment corresponding to a given appointment ID.
    public Appointment getAppointment(int appointmentID){
   	 {
	    	Appointment result = null;
	    	try
	    	{
		    	Statement stmt = conn.createStatement();
		    	ResultSet rs = stmt.executeQuery("select * from appointment where aid="+appointmentID+"");
		    	while (rs.next())
		    	{

		    		result = new Appointment(rs.getInt("aid"), rs.getInt("day"), rs.getInt("month"), rs.getInt("start"), rs.getInt("finish"), rs.getString("description"),
 		   				rs.getString("placeDescription"),new User(rs.getString("holds"),""));
		    	}
		    	rs.close();
	    	}
	    	catch (Exception e)
	    	{
	    		e.printStackTrace();
	    	}
	    	return result;
	   	} 
   	 }
    
	public void updateMeeting(Meeting meeting)
	{
		try{
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE meeting SET " +
					"day = "+meeting.getDay() +
					",month = "+meeting.getMonth() +
					",start = "+meeting.getStart() +
					",finish = "+meeting.getEnd() +
					",description = '"+meeting.getDescription()+"'" +
					",cancel = "+meeting.getCanceled() +
					",externalAttendees = "+meeting.getExternalAttendees() +
					"WHERE aid = "+meeting.getID());
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public void updateAppointment(Appointment appointment)
	{
		try{
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE appointment SET " +
					"day = "+appointment.getDay() +
					",month = "+appointment.getMonth() +
					",start = "+appointment.getStart() +
					",finish = "+appointment.getEnd() +
					",description = '"+appointment.getDescription()+"'" +
					",placeDescription = '"+appointment.getPlace()+"'"+
					"WHERE aid = "+appointment.getID());
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
}

